﻿using CommonUtils;
using DatabaseService.Model;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Reflection;

namespace DatabaseService.DatabaseDAO
{
    /// <summary>
    /// 数据库操作类
    /// </summary>
    public class DatabaseServiceDAO : IDatabaseDAO
    {
        /// <summary>
        /// 数据库执行对象
        /// </summary>
        public IDatabaseService databaseService { get; set; }

        #region 接口优化
        /// <summary>
        /// 改
        /// </summary>
        /// <typeparam name="T">数据库表实体类</typeparam>
        /// <param name="parametersModel">参数实体类</param>
        /// <returns>sql执行影响的行数</returns>
        public int Update<T>(DaoParametersEntity parametersModel) where T : TableModelMapper, new()
        {
            if (parametersModel == null)
                return 0;
            return this.ExecuteSqlNonQuery<T>(parametersModel);
        }
        /// <summary>
        /// 增
        /// </summary>
        /// <typeparam name="T">数据库表实体类</typeparam>
        /// <param name="parametersModel">参数实体类</param>
        /// <returns>sql执行影响的行数</returns>
        public int Insert<T>(DaoParametersEntity parametersModel) where T : TableModelMapper, new()
        {
            if (parametersModel == null)
                return 0;
            return this.ExecuteSqlNonQuery<T>(parametersModel);
        }
        /// <summary>
        /// 删
        /// </summary>
        /// <typeparam name="T">数据库表实体类</typeparam>
        /// <param name="parametersModel">参数实体类</param>
        /// <returns>sql执行影响的行数</returns>
        public int Delete<T>(DaoParametersEntity parametersModel) where T : TableModelMapper, new()
        {
            if (parametersModel == null)
                return 0;
            return this.ExecuteSqlNonQuery<T>(parametersModel);
        }
        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T">数据库表实体类</typeparam>
        /// <param name="parametersModel">参数实体类</param>
        /// <returns>IList<T> 结果集转化List</returns>
        public IList<T> Select<T>(DaoParametersEntity parametersModel) where T : TableModelMapper, new()
        {
            if (parametersModel == null)
                return new List<T>();

            string method = parametersModel.GetMethodName();
            if (string.IsNullOrWhiteSpace(method))
                return new List<T>();

            string xmlsql;
            xmlsql = StaticTableModelSqlCache<T>.Getsql(method, getIDictionaryStr(parametersModel.GetParameters()));
            
            return this.ExcuteGetList<T>(xmlsql, parametersModel.GetParameters());
        }
        /// <summary>
        /// 查询值
        /// </summary>
        /// <typeparam name="T">数据库表实体类</typeparam>
        /// <typeparam name="T1">需要返回的类型</typeparam>
        /// <param name="parametersModel">参数实体类</param>
        /// <returns>sql执行的第一行第一列的值</returns>
        public T1 Select_Value<T, T1>(DaoParametersEntity parametersModel) where T : TableModelMapper, new()
        {
            T1 tentity = default(T1);
            if (parametersModel == null)
                return tentity;

            string method = parametersModel.GetMethodName();
            if (string.IsNullOrWhiteSpace(method))
                return tentity;
            string xmlsql;
            xmlsql = StaticTableModelSqlCache<T>.Getsql(method, getIDictionaryStr(parametersModel.GetParameters()));
            try
            {
                var converter = TypeDescriptor.GetConverter(typeof(T1)); //获取转化类
                DataTable dataTable = this.Excute(xmlsql, parametersModel.GetParameters());
                if (dataTable != null && dataTable.Rows.Count > 0)
                    tentity = (T1)(converter.ConvertFromString(dataTable.Rows[0][0].ToString().Trim()));
                    //tentity = (T1)(converter.ConvertTo(dataTable.Rows[0][0].ToString().Trim(), typeof(T1)));
                    //tentity = (T1)((object)dataTable.Rows[0][0].ToString());
                    //tentity = (T1)dataTable.Rows[0][0];
            }
            catch (Exception ex)
            {
                tentity = default(T1);
                //记录日志
                LogRun.Error("DatabaseServiceDAO.Select_Value error:" + ex.Message);
                LogRun.Error("DatabaseServiceDAO.Select_Value error:" + ex.StackTrace);
            }

            return tentity;
        }
        /// <summary>
        /// 存储过程执行
        /// </summary>
        /// <param name="parametersModel">参数实体类</param>
        /// <returns>DataSet 过程执行结果集</returns>
        public DataSet ExecuteProcGetDataSet(DaoParametersEntity parametersModel)
        {
            if (parametersModel == null)
                return null;
            string method = parametersModel.GetMethodName();
            if (string.IsNullOrWhiteSpace(method))
                return null;

            string storedProcedureContent = StaticTableModelSqlCache<ProcedureEntityMapper>.Getsql(method, getIDictionaryStr(parametersModel.GetParameters()));
            if (string.IsNullOrWhiteSpace(storedProcedureContent))
                return null;

            //ProcduceLogInfo(parametersModel);  //记录到日志

            DataSet dataSet = null;
            try
            {
                //1.storedProcedureContent等于storedProcedureName
                if(storedProcedureContent.Split(' ').Count() == 1)
                    dataSet = ExcuteProcGetData(storedProcedureContent, parametersModel.GetParameters());
                else  //2.storedProcedureContent为执行sql
                    dataSet = databaseService.ExecuteProcSql(storedProcedureContent, parametersModel.GetParameters());
            }
            catch (Exception exc)
            {
                LogRun.Error("DatabaseServiceDAO.ExecuteProcGetDataSet error:" + exc.Message);
                LogRun.Error("DatabaseServiceDAO.ExecuteProcGetDataSet error:" + exc.StackTrace);
                throw exc; //过程直接抛出异常
            }

            return dataSet;
        }
        /// <summary>
        /// 存储过程执行
        /// 类型相同的结果集
        /// </summary>
        /// <typeparam name="T">结果集实体类</typeparam>
        /// <param name="parametersModel">参数实体类</param>
        /// <returns>IList<T> 过程执行结果集转化List</returns>
        public IList<T> ExecuteProcGetList<T>(DaoParametersEntity parametersModel) where T : ProcedureEntityMapper
        {
            if (parametersModel == null)
                return null;

            string method = parametersModel.GetMethodName();
            if (string.IsNullOrWhiteSpace(method))
                return null;
            
            string storedProcedureContent = StaticTableModelSqlCache<ProcedureEntityMapper>.Getsql(method, getIDictionaryStr(parametersModel.GetParameters()));
            if (string.IsNullOrWhiteSpace(storedProcedureContent))
                return null;

            List<T> list = new List<T>();
            DataSet dataSet = null;
            try
            {
                //1.storedProcedureContent等于storedProcedureName
                if (storedProcedureContent.Split(' ').Count() == 1)
                    dataSet = ExcuteProcGetData(storedProcedureContent, parametersModel.GetParameters());
                else  //2.storedProcedureContent为执行sql
                    dataSet = databaseService.ExecuteProcSql(storedProcedureContent, parametersModel.GetParameters());
                if (dataSet != null && dataSet.Tables.Count > 0)
                {
                    foreach (DataTable dataTable in dataSet.Tables)
                    {
                        list.AddRange(ConvertToList<T>(dataTable));
                    }
                    list = list.Where(p => p != null).ToList();
                }
            }
            catch (Exception ee)
            {
                LogRun.Error("DatabaseServiceDAO.ExecuteProcGetList error:" + ee.Message);
                LogRun.Error("DatabaseServiceDAO.ExecuteProcGetList error:" + ee.StackTrace);
                throw ee; //过程直接抛出异常
            }

            return list;
        }
        
        #endregion

        #region 私有公用方法
        /// <summary>
        /// 增删改
        /// </summary>
        /// <typeparam name="T">数据库表实体类</typeparam>
        /// <param name="parametersModel">参数实体类</param>
        /// <returns>sql执行影响的行数</returns>
        private int ExecuteSqlNonQuery<T>(DaoParametersEntity parametersModel) where T : TableModelMapper, new()
        {
            string method = parametersModel.GetMethodName();
            if (string.IsNullOrWhiteSpace(method))
                return 0;

            string xmlsql = StaticTableModelSqlCache<T>.Getsql(method, getIDictionaryStr(parametersModel.GetParameters()));
            //if (string.IsNullOrWhiteSpace(xmlsql))
            //    return 0;

            return this.ExecuteNonQuery(xmlsql, parametersModel.GetParameters());
        }
        /// <summary>
        /// 执行sql
        /// </summary>
        /// <typeparam name="T">需转化的类型</typeparam>
        /// <param name="sql">sql文本</param>
        /// <param name="parameters">sql参数列表</param>
        /// <returns>结果集List</returns>
        private IList<T> ExcuteGetList<T>(string sql, IDictionary<string, object> parameters)
        {
            IList<T> list = new List<T>();
            if (string.IsNullOrWhiteSpace(sql))
                return list;
            try
            {
                DataTable dataTable = this.Excute(sql, parameters);
                if (dataTable != null && dataTable.Rows.Count > 0)
                    list = ConvertToList<T>(dataTable);
                //else
                //    list = new List<T>();
            }
            catch (Exception e)
            {
                //记录日志
                LogRun.Error("DatabaseServiceDAO.ExcuteGetList error:" + e.Message);
                LogRun.Error("DatabaseServiceDAO.ExcuteGetList error:" + e.StackTrace);
            }

            return list;
        }
        /// <summary>
        /// 查询sql执行
        /// </summary>
        /// <param name="sqlText">sql文本</param>
        /// <param name="parameters">sql参数列表</param>
        /// <returns></returns>
        private DataTable Excute(string sqlText, IDictionary<string, object> parameters = null)
        {
            if (string.IsNullOrWhiteSpace(sqlText))
                return null;
            try
            {
                return databaseService.Select(sqlText, parameters);
            }
            catch (Exception e)
            {
                //记录日志
                LogRun.Error("DatabaseServiceDAO.Excute error:" + e.Message);
                LogRun.Error("DatabaseServiceDAO.Excute error:" + e.StackTrace);
                return null;
            }
        }

        /// <summary>
        /// 增删改sql执行
        /// </summary>
        /// <param name="sqlText">sql文本</param>
        /// <param name="parameters">sql参数列表</param>
        /// <returns>sql执行影响的行数</returns>
        private int ExecuteNonQuery(string sqlText, IDictionary<string, object> parameters)
        {
            if (string.IsNullOrWhiteSpace(sqlText))
                return 0;
            int ret;
            try
            {
                ret = databaseService.ExecuteNonQuery(sqlText, parameters);
            }
            catch (Exception e)
            {
                //记录日志
                ret = 0;
                LogRun.Error("DatabaseServiceDAO.ExecuteNonQuery error:" + e.Message);
                LogRun.Error("DatabaseServiceDAO.ExecuteNonQuery error:" + e.StackTrace);
            }

            return ret;
        }

        private DataSet ExcuteProcGetData(string storedProcedureName, IDictionary<string, object> parameters = null)
        {
            return this.databaseService.ExecuteProcGetDataSet(storedProcedureName, parameters);
        }

        /// <summary>
        /// DataTable转换为List
        /// </summary>
        /// <typeparam name="T">需转化的类型</typeparam>
        /// <param name="dataTable">数据源</param>
        /// <returns>转化后的List</returns>
        private IList<T> ConvertToList<T>(DataTable dataTable)
        {
            //IList<T> list = null;
            IList<T> list = new List<T>();
            if (dataTable != null && dataTable.Rows.Count > 0)
            {
                list = new List<T>();
                PropertyInfo[] propertyInfos = typeof(T).GetProperties();
                foreach (DataRow row in dataTable.Rows)
                {
                    T entity = (T)Activator.CreateInstance(typeof(T));
                    foreach (DataColumn col in dataTable.Columns)
                    {
                        //忽略大小写 --- 有的数据库字段名不符合C#命名规范 如class ，可改成 Class
                        //var proInfo = propertyInfos.Where(p => p.Name.Equals(col.ColumnName)).FirstOrDefault();
                        var proInfo = propertyInfos.Where(p => p.Name.ToLower().Equals(col.ColumnName.Replace("_","").ToLower())).FirstOrDefault();
                        if (proInfo == null)
                            continue;
                        ////如果为空
                        //row[proInfo.Name].ToString()  ---> ""

                        if (proInfo.PropertyType.Name.Equals("String"))
                            proInfo.SetValue(entity, row[col.ColumnName].ToString().Trim(), null);
                        else if (proInfo.PropertyType.Name.Equals("Int32") || proInfo.PropertyType.BaseType.Name.Equals("Enum"))
                            proInfo.SetValue(entity, Common.getInt(row[col.ColumnName].ToString()), null);
                        else if (proInfo.PropertyType.Name.Equals("Int64"))
                            proInfo.SetValue(entity, Common.getLong(row[col.ColumnName].ToString()), null);
                        else if (proInfo.PropertyType.Name.Equals("Single"))
                            proInfo.SetValue(entity, Common.getFloat(row[col.ColumnName].ToString()), null);
                        else if (proInfo.PropertyType.Name.Equals("Double"))
                            proInfo.SetValue(entity, Common.getDouble(row[col.ColumnName].ToString()), null);
                        else if (proInfo.PropertyType.Name.Equals("DateTime"))
                            proInfo.SetValue(entity, Common.getDateTime(row[col.ColumnName].ToString()), null);
                    }
                    list.Add(entity);
                }
            }

            return list;
        }

        /// <summary>
        /// 获取数据库版本
        /// </summary>
        /// <returns></returns>
        private string GetDBVersion()
        {
            string dbVersion = "";
            try
            {
                //string sql = "select sqlite_version()";
                //DataTable result = this.Excute(sql, null);
                //dbVersion = result.Rows[0][0].ToString() ?? "";
                DaoParametersEntity entity = new DaoParametersEntity("GetDBVersion");
                dbVersion = this.Select_Value<SystemCommonMapper, string>(entity);
            }
            catch (Exception ex)
            { }

            return dbVersion;
        }

        /// <summary>
        /// 存日志用,取IDictionary成员拼接成的字符串
        /// </summary>
        /// <param name="dictionary"></param>
        /// <returns></returns>
        private string getIDictionaryStr(IDictionary<string, object> dictionary)
        {
            string s = "";
            if (dictionary != null && dictionary.Count > 0)
            {
                foreach (string key in dictionary.Keys)
                {
                    s += key + ":" + dictionary[key] + ",";
                }
            }
            return s;
        }

        /// <summary>
        /// 记录存储过程执行日志
        /// </summary>
        /// <param name="entity">传入参数</param>
        private void ProcduceLogInfo(DaoParametersEntity entity)
        {
            //记录日志
            //LogToFile.WritrLogToFile("Proc", "ProduceName：" + entity.GetMethodName());
            //LogToFile.WritrLogToFile("ProcParam", getIDictionaryStr(entity.GetParameters()));
        }

        #endregion

        #region 可重用方法
        /// <summary>
        /// 检查数据库链接
        /// </summary>
        /// <returns></returns>
        public bool CheckDBconn()
        {
            if (string.IsNullOrWhiteSpace(GetDBVersion()))
                return false;
            return true;
        }
        /// <summary>
        /// 是否存在记录
        /// </summary>
        /// <typeparam name="T">数据库表实体类</typeparam>
        /// <param name="parametersModel">参数实体类</param>
        /// <returns></returns>
        public bool Exists<T>(DaoParametersEntity parametersModel) where T : TableModelMapper, new()
        {
            string method = parametersModel.GetMethodName();
            if (string.IsNullOrWhiteSpace(method))
                return false;
            //有反射转化,效率低
            //var data = Select<T>(method, parameters);
            string sql = StaticTableModelSqlCache<T>.Getsql(method, getIDictionaryStr(parametersModel.GetParameters()));
            var data = this.Excute(sql, parametersModel.GetParameters());
            if (data != null && data.Rows.Count > 0)
                return true;
            return false;
        }
        /// <summary>
        /// 检验某表是否存在某字段名
        /// </summary>
        /// <param name="tableName">数据库表名</param>
        /// <param name="columnName">字段名</param>
        /// <returns></returns>
        public bool ExistsColumn(string tableName, string columnName)
        {
            try
            {
                string sql = $"select * from sqlite_master where name='{tableName}' and sql like '%{columnName}%'";
                return this.Excute(sql, null).Rows.Count > 0;
            }
            catch (Exception e)
            {
                //记录日志
                LogRun.Error("DatabaseServiceDAO.ExistsColumn error:" + e.Message);
                LogRun.Error("DatabaseServiceDAO.ExistsColumn error:" + e.StackTrace);
                return false;
            }
        }
        #endregion
    }
}
